import json
import pandas as pd
import sys
import os



save_path1 = '/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_数据分析_2023-11-06_11_30_26.xlsx'
# 解析JSON文件:json文内容，一行包含了所有数据, 反序列化最外层字典和字典内col_20值
data_list = []
with open('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_数据分析_2023-11-06_11_30_26.json', 'r') as f:

    # 拆分json数据，储存为列表[{},{},{}]
    for i in f:
        print(type(i),len(i))
        json_data_list = i.replace('}{','}露露{').split('露露')
    #
    for i in range(len(json_data_list)):
        dic = json.loads(json_data_list[i])
        dic['respData'] = json.loads(dic['respData'])
        data_list.append(dic)

        print(type(data_list[i]),data_list[i])


df = pd.DataFrame(data_list)
# df.to_excel(save_path,index=False)


# 读取Excel文件
# excel_data = pd.read_excel(save_path)
# 将某一列的数据类型转换为字符串型
for i in ['reqChatTime','respCtrlScreenTime','reqContentTime','reqGenSql','respSql',
                         'execSqlTime','sqlToJsonTime','reqWenxinChat','respWenxinChat',
                         'endTime','totalCost','intentCost','genSqlCost',
                         'execSqlCost','genChatCost']:
    df[i] = df[i].astype(str)
# 将修改后的数据保存到新的Excel文件中
df.to_excel(save_path1, index=False)



